/**
 * Collaborative Story Development Web Application (CSDApp) Copyright Anton
 * Strack 2014
 *
 * This file is part of Collaborative Story Development Application (CSDApp).
 *
 * CSDApp is free software: you can redistribute it and/or modify it under the
 * terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or any later version.
 *
 * CSDApp is distributed in the hope that it will be useful, but WITHOUT ANY
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * CSDApp. If not, see <http://www.gnu.org/licenses/>.
 *
 */
package csdwa;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.EnumMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * This class performs the majority of work in creating queries and setting up
 * queryMarkers for the dao.
 *
 * This class contains four default template queries, SELECT_QUERY,
 * INSERT_QUERY, UPDATE_QUERY, and DELETE_QUERY that can suffice for most query
 * types as the child dao class is expected to properly set up the QueryMarkers
 * per request. Nonetheless, child dao classes can implement their own advanced
 * queries if needed, but they'll only get support from the methods in this
 * class if they follow a QueryMarker template style.
 *
 * @author Anton Strack
 */
public abstract class DaoSupport {

    /**
     * Internal processing code indicating that only the column section of the
     * derived marker is being used. This would be the case when the derived
     * marker is used for adding a function such as COUNT(columnId) and thus
     * there is no need to add a table or condition.
     */
    protected final int ONE_DERIVED_MARKER = 1;
    /**
     * Internal processing code indicating that the derived value contains a
     * column, table, and condition.
     */
    protected final int ALL_DERIVED_MARKERS = 3;
    /**
     * Internal processing code indicating that no derived markers are being
     * used.
     */
    protected final int NO_DERIVED_MARKERS = 0;
    /**
     * The columns to update. The update methods in this class uses this
     * variable to determine how many and which columns are to be updated.
     */
    protected String COLUMNS_TO_UPDATE;
    /**
     * The connnection to the database used by the daos.
     */
    protected Connection conn;
    /**
     * The prepared statement to be used when issuing queries from daos.
     */
    protected PreparedStatement ps;
    /**
     * The query markers containing the dao specific values.
     */
    protected Map<QueryMarker, String> queryMarkers;
    /**
     * The query markers to use for the specific query. Only the query markers
     * in this list will be added to the query templates for execution.
     */
    protected List<QueryMarker> usingQueryMarkers;
    /**
     * Select query template.
     */
    protected final String SELECT_QUERY = "SELECT " + QueryMarker.CORE_COLUMNS + QueryMarker.RELATED_COLUMNS + QueryMarker.DERIVED_COLUMNS + " FROM " + QueryMarker.CORE_TABLE + QueryMarker.RELATED_TABLES + QueryMarker.DERIVED_TABLES + " WHERE " + QueryMarker.CORE_CONDITIONS + QueryMarker.RELATED_CONDITIONS + QueryMarker.DERIVED_CONDITIONS + " ORDER BY " + QueryMarker.ORDER_BY_COLUMN + " " + QueryMarker.ORDER_BY_MODE + " " + QueryMarker.LIMIT;
    /**
     * Insert query template.
     */
    protected final String INSERT_QUERY = "INSERT INTO " + QueryMarker.CORE_TABLE + " (" + QueryMarker.CORE_COLUMNS + ") VALUES (" + QueryMarker.INSERT_VALUES + ")";
    /**
     * Update query template
     */
    protected final String UPDATE_QUERY = "UPDATE " + QueryMarker.CORE_TABLE + " SET " + QueryMarker.UPDATE_COLUMNS_VALUES + " WHERE " + QueryMarker.CORE_CONDITIONS;
    /**
     * Delete query template.
     */
    protected final String DELETE_QUERY = "DELETE FROM " + QueryMarker.CORE_TABLE + " WHERE " + QueryMarker.CORE_CONDITIONS;
    /**
     * If select queries should use the related query markers. Using related
     * query markers will mean joining multiple addiitional tables to provide
     * names and descriptions for foreign keys.
     */
    protected boolean USE_RELATED_MARKERS = false;

    protected DaoSupport(Connection conn) {
        this.conn = conn;
        this.queryMarkers = new EnumMap<QueryMarker, String>(QueryMarker.class);
        this.usingQueryMarkers = new ArrayList<QueryMarker>();
    }

    /**
     * This method is intended to be called by the child constructor to setup
     * the default queryMarkers for the dao. This is provided in a method to
     * allow for resetting the markers to their original setup since calling
     * classes can alter them.
     */
    public abstract void restoreQueryMarkers();

    /**
     * This method splits comma delimited strings and trims commas from the
     * beginning and end of strings. Additionally, it cleans up the query by
     * removing additional spaces that might be left behind from the query
     * markers.
     *
     * @param stringToSplit A comma delimited list of items as a single string.
     * @return an array of strings.
     */
    public String[] splitStringByCommas(String stringToSplit) {
        if (stringToSplit.startsWith(",")) {
            // Remove any preceding commas as the split string command will unintentionally create an empty string at the 0 index.
            stringToSplit = stringToSplit.substring(1);
        }
        if (stringToSplit.endsWith(",")) {
            // remove any trailing commas it will cause problems with queries not expecting the trailing commas.
            int cutIndex = stringToSplit.lastIndexOf(",") - 1;
            stringToSplit = stringToSplit.substring(0, cutIndex);
        }
        stringToSplit = stringToSplit.trim();
        String onCommaAndOrSpace = "(,\\s*)"; // this regex will match comma and space delimitors or just comma delimiters. thus it won't break if the update columns marker value is inconsistent as long as it has a comma delimiter.
        String[] values = stringToSplit.split(onCommaAndOrSpace);
        return values;
    }

    /**
     * This method provides a means to indicate to the dao if it should query
     * the related tables in order to get the values matching the foriegn keys.
     * It is up to the child dao to select and add the related query markers if
     * this is set to true.
     *
     * @param use true to add the related query markers and false to not add
     * them.
     */
    public void setUseRelatedMarkers(boolean use) {
        this.USE_RELATED_MARKERS = use;
    }

    /**
     * Prepare the SET section of an update query with the column name = ?. Pass
     * in a string array to ensure the order used in preparing the SET section
     * is the same order used when setting the prepared statement values.
     *
     * @param cols
     * @return
     */
    public String prepareUpdateColumns(String[] cols) {
        StringBuilder sb = new StringBuilder();
        int colCount = cols.length - 1; // Subtract so that the for-loop ends 1 earlier and thus can append the value without the trailing comma. This way avoids a if condition in the for loop. As this is an update query, there must always be one set value.
        int i; // i needs to be accessible outside of the for loop.
        for (i = 0; i < colCount; i++) {
            sb.append(cols[i]).append(" = ?,");
        }
        sb.append(cols[i]).append(" = ?");
        // System.out.println("the update set section is:" + sb.toString());

        return sb.toString();
    }

    /**
     * This method builds the query string by replacing the query markers with
     * the values from the dao according to the list of query markers to use for
     * the query.
     *
     * @param usingQueryMarkers the list of query markers to use in the query
     * @param query the query template string
     * @return a fully formed sql query.
     */
    protected String buildQueryString(List<QueryMarker> usingQueryMarkers, String query) {
        for (QueryMarker qm : usingQueryMarkers) {
            query = query.replace(qm.toString(), this.getMarker(qm));
        }
        query = this.cleanQuery(query);
        return query;
    }

    /**
     * This method removes query markers that aren't remain after processing the
     * usingQueryMarkers list.
     *
     * @param query The sql query template
     * @return a cleaned up sql query.
     */
    private String cleanQuery(String query) {
        String onMarker = "\\$\\{[a-zA-Z]*\\}"; // matches ${text}
        String onTwoSpaces = "\\ {2}"; // matches 2 consecutive spaces
        String trailiingComma = ",\\s*\\)"; // matches a trailing comma any amount of whitespace and a closing ) thus , ) and ,)
        query = query.replaceAll(onMarker, ""); // remove all query markers
        query = query.replaceAll(onTwoSpaces, " ");// turn 2 spaces into one space
        query = query.replaceAll(trailiingComma, ")"); // remove the comma and whitespaces an just keep the closing )
        query = query.trim();
        return query;
    }

    /**
     * This method allows code outside of the DAO to set the values of query
     * markers such as the value of Limit and the column to order a query result
     * by.
     *
     * @param qm The query marker to add or update.
     * @param value the value specific to the query marker.
     */
    public final void setMarker(QueryMarker qm, String value) {
        this.queryMarkers.put(qm, value);
    }

    /**
     * this method provides access to the value of a specific query marker for
     * code outside of the DAO.
     *
     * @param qm the query marker of the specific value.
     * @return the value of the query marker.
     */
    public String getMarker(QueryMarker qm) {
        if (this.queryMarkers.containsKey(qm) == true && this.queryMarkers.get(qm) != null) {
            return this.queryMarkers.get(qm);
        }
        return "";
    }

    /**
     * Default usingQueryMarkers values for the select query.
     *
     * @return a default list of query markers used by the select query. These
     * markers represent the minimum needed for the select query.
     */
    protected List<QueryMarker> getCoreSelectUsingQueryMarkers() {

        this.usingQueryMarkers.add(QueryMarker.CORE_TABLE);
        this.usingQueryMarkers.add(QueryMarker.CORE_COLUMNS);
        this.usingQueryMarkers.add(QueryMarker.CORE_CONDITIONS);
        this.usingQueryMarkers.add(QueryMarker.ORDER_BY_COLUMN);
        this.usingQueryMarkers.add(QueryMarker.ORDER_BY_MODE);
        if (this.USE_RELATED_MARKERS == true) {
            this.usingQueryMarkers.add(QueryMarker.RELATED_COLUMNS);
            this.usingQueryMarkers.add(QueryMarker.RELATED_CONDITIONS);
            this.usingQueryMarkers.add(QueryMarker.RELATED_TABLES);
        }
        int usingDerivedMarkers = this.isUsingDerivedValues();
        if (usingDerivedMarkers == this.ALL_DERIVED_MARKERS || usingDerivedMarkers == this.ONE_DERIVED_MARKER) {
            this.usingQueryMarkers.add(QueryMarker.DERIVED_COLUMNS);
            if (usingDerivedMarkers == this.ALL_DERIVED_MARKERS) {
                this.usingQueryMarkers.add(QueryMarker.DERIVED_CONDITIONS);
                this.usingQueryMarkers.add(QueryMarker.DERIVED_TABLES);
            }
        }
        return this.usingQueryMarkers;
    }

    /**
     * Execute the select query.
     *
     * @param ps the prepared statement containing the select query and values
     * @return the number of rows selected or 0 if none were selected.
     */
    protected abstract int executeSelectQuery(PreparedStatement ps);

    /**
     * @return the columns to be updated.
     */
    public String getColumnsToUpdate() {
        return COLUMNS_TO_UPDATE;
    }

    /**
     * @param COLUMNS_TO_UPDATE the COLUMNS_TO_UPDATE to set
     */
    public void setColumnsToUpdate(String COLUMNS_TO_UPDATE) {
        this.COLUMNS_TO_UPDATE = COLUMNS_TO_UPDATE;
    }

    /**
     * Add a derived value to a select query. This method is used to build the
     * derived query markers.
     *
     * @param derivedColumn the column name that is to be selected. This is the
     * minimum requirement to use the derived value.
     * @param derivedTable the table the derived column is from if not the core
     * table
     * @param derivedCondition the condition needed to select the proper derived
     * from another table * value.
     */
    public void addDerivedValue(String derivedColumn, String derivedTable, String derivedCondition) {
        if (derivedColumn.length() > 0) {
            StringBuilder column = new StringBuilder();
            column.append(this.getMarker(QueryMarker.DERIVED_COLUMNS));
            column.append(", ");
            column.append(derivedColumn);
            this.setMarker(QueryMarker.DERIVED_COLUMNS, column.toString());
        }
        if (derivedTable.length() > 0) {
            StringBuilder table = new StringBuilder();
            table.append(this.getMarker(QueryMarker.DERIVED_TABLES));
            table.append(", ");
            table.append(derivedTable);
            this.setMarker(QueryMarker.DERIVED_TABLES, table.toString());
        }
        if (derivedCondition.length() > 0) {
            StringBuilder condition = new StringBuilder();
            condition.append(this.getMarker(QueryMarker.DERIVED_CONDITIONS));
            condition.append(" AND ");
            condition.append(derivedCondition);
            this.setMarker(QueryMarker.DERIVED_CONDITIONS, condition.toString());
        }
    }

    /**
     * Check to see if at least the derived column marker has content in it.
     *
     * @return 1 if just the derived column marker has content in it or if all
     * three derived markers have content in them return 3, Otherwise return 0.
     */
    public int isUsingDerivedValues() {
        int using = this.NO_DERIVED_MARKERS;
        if (this.getMarker(QueryMarker.DERIVED_COLUMNS).length() > 0 && this.getMarker(QueryMarker.DERIVED_CONDITIONS).length() > 0 && this.getMarker(QueryMarker.DERIVED_TABLES).length() > 0) {
            using = this.ALL_DERIVED_MARKERS;
        } else if (this.getMarker(QueryMarker.DERIVED_COLUMNS).length() > 0 && this.getMarker(QueryMarker.DERIVED_CONDITIONS).length() == 0 && this.getMarker(QueryMarker.DERIVED_TABLES).length() == 0) {
            using = this.ONE_DERIVED_MARKER;
        }
        return using;
    }

    /**
     * This method extracts the derived values from the result set as indicated
     * by the derived columns query marker. This method then adds the values to
     * the bean's derived value map using the column name as the key.
     *
     * @param bean the bean being processed in the select query
     * @param rs the result set from the select query
     */
    public void processDerivedValues(Resource bean, ResultSet rs) {
        if (this.getMarker(QueryMarker.DERIVED_COLUMNS).length() > 0) {
            String[] cols = this.splitStringByCommas(this.getMarker(QueryMarker.DERIVED_COLUMNS));

            for (String column : cols) {
                try {
                    /**
                     * to avoid the jsps needing to know tables, many derived
                     * columns us AS to create aliases and so the result set
                     * stores the columns by those aliases. Thus need to convert
                     * the full column name; tableName.columnName AS aliasName
                     * into just aliasName for retrieving the value.
                     */
                    int asIndex = column.indexOf(" AS ");
                    if (asIndex >= 0) {
                        int subStringIndex = asIndex + 4; // want to start after AS + space.
                        column = column.substring(subStringIndex);// return the column name after AS.
                    }
                    bean.setDerivedValue(column, rs.getString(column));
                } catch (SQLException ex) {
                    Logger.getLogger(DaoSupport.class.getName()).log(Level.SEVERE, null, ex);
                    ex.printStackTrace();
                } catch (Exception ex) {
                    Logger.getLogger(DaoSupport.class.getName()).log(Level.SEVERE, null, ex);
                    ex.printStackTrace();
                }
            }
        }
    }

    /**
     * Add a query marker to the list of query markers that should be used in
     * the query.
     *
     * @param queryMarker the query marker to add.
     */
    public void addUsingQueryMarker(QueryMarker queryMarker) {
        this.usingQueryMarkers.add(queryMarker);

    }

    /**
     * Get the query marker value according to the provided query marker.
     *
     * @param queryMarker the query marker to get the value of
     * @return a string representing the dao value of the query marker.
     */
    public String getQueryMarkerValue(QueryMarker queryMarker) {
        if (this.queryMarkers.containsKey(queryMarker)) {
            return this.queryMarkers.get(queryMarker);
        } else {
            return "";
        }
    }

    /**
     * Set the value of the query marker for the specific dao object.
     *
     * @param queryMarker the query marker to add or update
     * @param value the value of the query marker.
     */
    public void setQueryMarkerValue(QueryMarker queryMarker, String value) {
        this.queryMarkers.put(queryMarker, value);
    }

    /**
     * @return the conn
     */
    public Connection getConn() {
        return conn;
    }

    /**
     * @param conn the conn to set
     */
    public void setConn(Connection conn) {
        this.conn = conn;
    }

    /**
     * This method prepares string, intended as a row of table columns, By
     * removing the dot and and anything before it from the right portion of the
     * string.
     *
     * @param queryMarkerValue the row of table columns to be prepared.
     * @return a comma delimited list of values without their table identifiers
     * or the dot.
     */
    public String prepareInsertColumns(String queryMarkerValue) {
        String[] columns = this.splitStringByCommas(queryMarkerValue);
        StringBuilder sb = new StringBuilder();
        for (String col : columns) {
            sb.append(this.removeTableIdentifyFromColumn(col)).append(", ");
        }
        return sb.toString();
    }

    /**
     * This method is basically a hack for the HSQLDB as it fails to properly
     * process insert queries that have table identifies such as table.column
     *
     * @param column the column to convert
     * @return the column without the table. prefix.
     */
    public String removeTableIdentifyFromColumn(String column) {
        int index = column.lastIndexOf("."); // find the last occurance of the dot to allow for removing multiple value dot indentifyers.
        if (index > -1) {
            column = column.substring(index + 1);
        }
        return column;
    }

    /**
     * Select the next selection of rows that aren't = to id and have a greater
     * updated date
     *
     * @param id the id to select the next rows after
     *  * @param limit the number of rows to select
     * @return the number of rows selected.
     */
    public int selectNext(int id, int limit) {
        int result = 0;
        this.setMarker(QueryMarker.LIMIT, "LIMIT " + limit);
        this.setMarker(QueryMarker.ORDER_BY_COLUMN, "UpdatedDate");
        this.setMarker(QueryMarker.ORDER_BY_MODE, "DESC");
        this.setQueryMarkerValue(QueryMarker.CORE_CONDITIONS, SELECT_QUERY);

        this.setMarker(QueryMarker.CORE_CONDITIONS, this.getMarker(QueryMarker.CORE_TABLE).toString() + ".StateID <> " + InfoAggregator.getInstance().getStateTypeId("priorversion"));
        if (id > 0) {
            this.setMarker(QueryMarker.CORE_CONDITIONS, this.getMarker(QueryMarker.CORE_CONDITIONS).toString() + " AND UpdatedDate >= (SELECT UpdatedDate FROM " + this.getMarker(QueryMarker.CORE_TABLE).toString() + " WHERE id = ?)");
        }

        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.usingQueryMarkers.add(QueryMarker.LIMIT);

        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            if (id > 0) {
                // only set this if we actually will use the id value.
                this.ps.setInt(1, id);
            }
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select the previous selection of rows that aren't = to id and have a
     * lesser updated date
     *
     * @param id the id to select the previous rows before
     * @param limit the number of rows to select
     * @return the number of rows selected.
     */
    public int selectPrevious(int id, int limit) {

        int result = 0;
        this.setMarker(QueryMarker.LIMIT, "LIMIT " + limit);
        this.setMarker(QueryMarker.ORDER_BY_COLUMN, "UpdatedDate");
        this.setMarker(QueryMarker.ORDER_BY_MODE, "DESC");
        this.setQueryMarkerValue(QueryMarker.CORE_CONDITIONS, SELECT_QUERY);

        this.setMarker(QueryMarker.CORE_CONDITIONS, this.getMarker(QueryMarker.CORE_TABLE).toString() + ".StateID <> " + InfoAggregator.getInstance().getStateTypeId("priorversion"));
        if (id > 0) {
            this.setMarker(QueryMarker.CORE_CONDITIONS, this.getMarker(QueryMarker.CORE_CONDITIONS).toString() + " AND UpdatedDate < (SELECT UpdatedDate FROM " + this.getMarker(QueryMarker.CORE_TABLE).toString() + " WHERE id = ?)");
        }

        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.usingQueryMarkers.add(QueryMarker.LIMIT);

        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            if (id > 0) {
                // only set this if we actually will use the id value.
                this.ps.setInt(1, id);
            }
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Select a single bean by checking if the search query matches in the name
     * or description field. The search is case insensitive.
     *
     * @param query a string query to search for.
     * @return the number of beans retrieved from the database
     */
    public int selectBeanSearchQuery(String query) {
        int result = 0;
        query = query.toLowerCase();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "LOWER(" + this.getMarker(QueryMarker.CORE_TABLE) + ".name) LIKE ?");
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setString(1, query);
            // this.ps.setString(2, query);
            result = executeSelectQuery(ps);
        } catch (SQLException ex) {
            Logger.getLogger(PostDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
}
